use master
go

create database Student

on
(
	name='Student',
	filename='D:\sql\Student.mdf',
	size=5mb,
	maxsize=10mb,
	filegrowth=10%
)
log on
(
	name='Student_log',
	filename='D:\sql\Student.ldf',
	size=5mb,
	maxsize=10mb,
	filegrowth=10%
)
go


use Student
go

create table Class
(
	ClassID int primary key identity(1,1),
	ClassName nvarchar(20) unique not null
)


create table Student
(
	StuID int primary key identity(1,1),
	ClassID int references Class(ClassID),
	StuName nvarchar(20) not null,
	StuSex nvarchar(1) check(StuSex='男' or StuSex='女'),
	StuBirthday date ,
	StuPhone nvarchar(11)
)

create table Course
(
	CourseID int primary key identity(1,1),
	CourseName nvarchar(50) unique not null,
	CourseCredit int default(1) check(CourseCredit=1 or CourseCredit=2 or CourseCredit=3 or CourseCredit=4 or CourseCredit=5) 
)

create table Score 
(
	ScoreID int ,
	StuID int ,--关联学生表的StuID
	CourseID int, -- 关联课程信息表的课程编号
	Score decimal(5,2) unique not null
)

--在学生信息表加入地址
alter table Student add Stuadress nvarchar(200)
alter table Score add constraint FK_Score_StuID foreign key(StuID) references Student(StuID)
alter table Score add constraint FK_Score_CourseID foreign key(CourseID) references Course(CourseID)